﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace XChip_Common
{
    public class NPOIOprateExcel
    {

        /// <summary>  
        /// 将excel导入到datatable  
        /// </summary>  
        /// <param name="filePath">excel路径</param>  
        /// <param name="index">excel的第几个sheet</param>  
        /// <param name="isColumnName">第一行是否是列名</param>  
        /// <returns>返回datatable</returns>  
        public static DataTable ExcelToDataTable(string filePath, bool isColumnName, int index = 0)
        {
            DataTable dataTable = null;
            FileStream fs = null;
            DataColumn column = null;
            DataRow dataRow = null;
            IWorkbook workbook = null;
            ISheet sheet = null;
            IRow row = null;
            ICell cell = null;
            int startRow = 0;
            try
            {
                using (fs = File.OpenRead(filePath))
                {
                    // 2007版本  
                    if (filePath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(fs);
                    // 2003版本  
                    else if (filePath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(fs);

                    if (workbook != null)
                    {
                        sheet = workbook.GetSheetAt(index);//读取第一个sheet，当然也可以循环读取每个sheet  
                        dataTable = new DataTable();
                        if (sheet != null)
                        {
                            int rowCount = sheet.LastRowNum;//总行数  
                            if (rowCount > 0)
                            {
                                IRow firstRow = sheet.GetRow(0);//第一行  
                                int cellCount = firstRow.LastCellNum;//列数  

                                //构建datatable的列  
                                if (isColumnName)
                                {
                                    startRow = 1;//如果第一行是列名，则从第二行开始读取  
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        cell = firstRow.GetCell(i);
                                        if (cell != null)
                                        {
                                            if (cell.StringCellValue != null)
                                            {
                                                column = new DataColumn(cell.StringCellValue);
                                                dataTable.Columns.Add(column);
                                            }
                                        }
                                    }
                                }
                                else
                                {
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        column = new DataColumn("column" + (i + 1));
                                        dataTable.Columns.Add(column);
                                    }
                                }

                                //填充行  
                                for (int i = startRow; i <= rowCount; ++i)
                                {
                                    row = sheet.GetRow(i);
                                     //cellCount = row.LastCellNum;//列数  
                                    if (row == null) continue;
                                    if (i == 14)
                                    {

                                    }
                                    dataRow = dataTable.NewRow();
                                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                                    {
                                        cell = row.GetCell(j);
                                        if (cell == null)
                                        {
                                            dataRow[j] = "";
                                        }
                                        else
                                        {

                                            //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)  
                                            switch (cell.CellType)
                                            {

                                                case CellType.Blank:
                                                    dataRow[j] = "";
                                                    break;
                                                //case CellType.Numeric:
                                                //    break;
                                                //    if (cell.CellType == CellType.Numeric)
                                                //    {
                                                //        //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
                                                //        if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
                                                //        {
                                                //            dataRow[i] = cell.DateCellValue;
                                                //        }
                                                //        else//其他数字类型
                                                //        {
                                                //            dataRow[i] = cell.NumericCellValue;
                                                //        }
                                                //    }
                                                //    else if (cell.CellType == CellType.Blank)//空数据类型
                                                //    {
                                                //        dataRow[i] = "";
                                                //    }
                                                //    else if (cell.CellType == CellType.Formula)//公式类型
                                                //    {
                                                //        HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workbook);
                                                //        dataRow[i] = eva.Evaluate(cell).StringValue;
                                                //    }
                                                //    else //其他类型都按字符串类型来处理
                                                //    {
                                                //        dataRow[i] = cell.StringCellValue;
                                                //    }
                                                //short format = cell.CellStyle.DataFormat;
                                                ////对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理  
                                                ////if (format == 14 || format == 31 || format == 57 || format == 58)
                                                ////    dataRow[j] = cell.DateCellValue;
                                                ////else
                                                ////    dataRow[j] = cell.NumericCellValue;

                                                //if (format != 0)
                                                //{ dataRow[j] = cell.DateCellValue; }
                                                //else { dataRow[j] = cell.NumericCellValue; }


                                                case CellType.Unknown:
                                                case CellType.String:
                                                case CellType.Formula:
                                                case CellType.Boolean:
                                                case CellType.Error:
                                                default:

                                                    cell.SetCellType(CellType.String);
                                                    dataRow[j] = cell.StringCellValue;

                                                    break;


                                            }
                                        }
                                    }
                                    dataTable.Rows.Add(dataRow);
                                }
                            }
                        }
                    }
                }
                return dataTable;
            }
            catch (Exception ex)
            {
                if (fs != null)
                {
                    fs.Close();
                }
                return null;
            }
        }

        //public static DataTable ExcelToDataTables(string filePath, int index = 0,int cellCount = 0)
        //{
        //    DataTable dataTable = null; FileStream fs = null; DataColumn column = null; DataRow dataRow = null; IWorkbook workbook = null;
        //    ISheet sheet = null; IRow row = null; ICell cell = null; int startRow = 0;
        //    try{ using (fs = File.OpenRead(filePath)){
        //            // 2007版本  
        //            if (filePath.IndexOf(".xlsx") > 0)
        //                workbook = new XSSFWorkbook(fs);
        //            // 2003版本  
        //            else if (filePath.IndexOf(".xls") > 0)
        //                workbook = new HSSFWorkbook(fs);
        //            if (workbook != null)
        //            {sheet = workbook.GetSheetAt(index);//读取第一个sheet，当然也可以循环读取每个sheet  
        //                dataTable = new DataTable();
        //                if (sheet != null)
        //                {int rowCount = sheet.LastRowNum;//总行数  
        //                    if (rowCount > 0)
        //                    {IRow firstRow = sheet.GetRow(0);//第一行 
        //                        var firstRowNum = firstRow.FirstCellNum == -1?0: firstRow.FirstCellNum;
        //                        if (firstRow.LastCellNum != -1)cellCount = firstRow.LastCellNum;//列数  
        //                            for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
        //                            {
        //                                column = new DataColumn("column" + (i + 1));dataTable.Columns.Add(column);
        //                            }
        //                        for (int i = startRow; i <= rowCount; ++i)
        //                        {
        //                            row = sheet.GetRow(i);
        //                            if (row == null) continue;
        //                            dataRow = dataTable.NewRow();
        //                            for (int j = (row.FirstCellNum==-1?0: row.FirstCellNum); j < cellCount; ++j)
        //                            {
        //                                cell = row.GetCell(j);
        //                                if (row.GetCell(j) != null && !string.IsNullOrEmpty(row.GetCell(j).ToString()))
        //                                {
        //                                    dataRow[j] = row.GetCell(j).ToString();
        //                                }
        //                                else
        //                                {
        //                                    dataRow[j] = "";
        //                                }
        //                            }
        //                            dataTable.Rows.Add(dataRow);
        //                        }
        //                    }
        //                }
        //            }
        //        }
        //        return dataTable;
        //    }
        //    catch (Exception ex)
        //    {
        //        if (fs != null)
        //        {
        //            fs.Close();
        //        }
        //        return null;
        //    }
        //}


        public static DataTable ExcelToDataTables(string filePath, int index = 0, int cellCount = 0)
        {
            DataTable dataTable = null;
            using (FileStream fs = File.OpenRead(filePath))
            {
                IWorkbook workbook = null;
                ISheet sheet = null;
                IRow row = null;
                ICell cell = null;

                try
                {
                    // 2007版本  
                    if (filePath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(fs);
                    // 2003版本  
                    else if (filePath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(fs);

                    if (workbook != null)
                    {
                        sheet = workbook.GetSheetAt(index);

                        dataTable = new DataTable();

                        if (sheet != null)
                        {
                            int rowCount = sheet.LastRowNum;

                            if (rowCount > 0)
                            {
                                IRow firstRow = sheet.GetRow(0);
                                var firstCellNum = firstRow.FirstCellNum == -1 ? 0 : firstRow.FirstCellNum;
                                cellCount = firstRow.LastCellNum != -1 ? firstRow.LastCellNum : cellCount;

                                for (int i = firstCellNum; i < cellCount; ++i)
                                {
                                    var column = new DataColumn("column" + (i + 1));
                                    dataTable.Columns.Add(column);
                                }

                                for (int i = 0; i <= rowCount; ++i)
                                {
                                    row = sheet.GetRow(i);

                                    if (row == null) continue;

                                    var dataRow = dataTable.NewRow();

                                    for (int j = firstCellNum; j < cellCount; ++j)
                                    {
                                        cell = row.GetCell(j);

                                        if (cell != null)
                                        {
                                            dataRow[j] = cell.ToString();
                                        }
                                    }

                                    dataTable.Rows.Add(dataRow);
                                }
                            }
                        }
                    }

                    return dataTable;
                }
                catch (Exception ex)
                {
                    return null;
                }
            }
        }

        /// <summary>
        /// Excel转换成DataTable
        /// </summary>
        /// <param name="hSSFWorkbook"></param>
        /// <returns></returns>
        public static List<DataTable> ToExcelDataTable(IWorkbook hSSFWorkbook,int cellNo=0)
        {
            List<DataTable> datablelist = new List<DataTable>();
            for (int sheetIndex = 0; sheetIndex < hSSFWorkbook.NumberOfSheets; sheetIndex++)
            {
                ISheet sheet = hSSFWorkbook.GetSheetAt(sheetIndex);

                //获取表头FirstRowNum 第一行索引 0
                IRow header = sheet.GetRow(sheet.FirstRowNum);//获取第一行

                if (header == null)
                {
                    break;
                }
                int startRow = 0; //数据的第一行索引

                DataTable dtNpoi = new DataTable();
                startRow = sheet.FirstRowNum + 1;
                for (int i = header.FirstCellNum; i < header.LastCellNum; i++)
                {
                    ICell cell = header.GetCell(i);
                    if (cell != null)
                    {
                        string cellValue = $"Column{i + 1}_{cell.ToString()}";
                        if (cellValue != null)
                        {
                            DataColumn column = new DataColumn(cellValue);
                            dtNpoi.Columns.Add(column);
                        }
                        else
                        {
                            DataColumn column = new DataColumn();
                            dtNpoi.Columns.Add(column);
                        }
                    }
                }
                if (dtNpoi.Columns.Count ==0)
                {
                    for (int i = 0; i < cellNo; i++)
                    {
                        ICell cell = header.GetCell(i);
                        string cellValue = $"Column{i + 1}";
                        DataColumn column = new DataColumn(cellValue);
                        dtNpoi.Columns.Add(column);
                    }
                }

                //数据    LastRowNum最后一行的索引   如第九行----索引8
                for (int i = startRow; i < sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);//获取第i行
                    if (row == null)
                    {
                        continue;
                    }
                    DataRow dr = dtNpoi.NewRow();

                    //遍历每行的单元格
                    for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                    {
                        if (row.GetCell(j) != null&&!string.IsNullOrEmpty(row.GetCell(j).ToString()))
                        {
                            dr[j] = row.GetCell(j).ToString();
                        }
                    }
                    dtNpoi.Rows.Add(dr);
                }
                datablelist.Add(dtNpoi);
            }
            return datablelist;
        }



        /// <summary>
        /// 将DataTable数据导入到excel中
        /// </summary>
        /// <param name="data">要导入的数据</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <param name="sheetName">要导入的excel的sheet的名称</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public static int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten, string fileName)
        {
            int i = 0;
            int j = 0;
            int count = 0;
            ISheet sheet = null;
            IWorkbook workbook = null;
            FileStream fs = null;

            fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                workbook = new XSSFWorkbook();
            else if (fileName.IndexOf(".xls") > 0) // 2003版本
                workbook = new HSSFWorkbook();

            try
            {
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
                else
                {
                    return -1;
                }

                if (isColumnWritten == true) //写入DataTable的列名
                {
                    IRow row = sheet.CreateRow(0);
                    for (j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                    }
                    count = 1;
                }
                else
                {
                    count = 0;
                }

                for (i = 0; i < data.Rows.Count; ++i)
                {
                    IRow row = sheet.CreateRow(count);
                    for (j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                    }
                    ++count;
                }
                workbook.Write(fs); //写入到excel
                return count;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return -1;
            }
        }

        /// <summary>
        /// C# List转换成DataTable
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public static System.Data.DataTable ListToDataTable(IList list)
        {
            System.Data.DataTable result = new System.Data.DataTable();
            if (list.Count > 0)
            {
                PropertyInfo[] propertys = list[0].GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    //获取类型
                    Type colType = pi.PropertyType;
                    //当类型为Nullable<>时
                    if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                    {
                        colType = colType.GetGenericArguments()[0];
                    }
                    result.Columns.Add(pi.Name, colType);
                }
                for (int i = 0; i < list.Count; i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys)
                    {
                        object obj = pi.GetValue(list[i], null);
                        tempList.Add(obj);
                    }
                    object[] array = tempList.ToArray();
                    result.LoadDataRow(array, true);
                }
            }
            return result;
        }


        public static List<string> GetSheetName(IWorkbook workbook)
        {
            List<string> sheetTemp = new List<string>();
            for (int i = 0; i < 1000; i++)
            {
                try
                {
                    if (!string.IsNullOrEmpty(workbook.GetSheetName(i)))
                    {
                        sheetTemp.Add(workbook.GetSheetName(i));
                    }
                }
                catch (Exception)
                {
                    break;
                }
            }
            return sheetTemp;
        }
        public static ICellStyle GetExcelStyle(HSSFWorkbook hssfworkbook, HorizontalAlignment alignment, VerticalAlignment vlignment, short color, double fontNo)
        {
            ICellStyle cellstyle = hssfworkbook.CreateCellStyle();
            cellstyle.Alignment = alignment;
            cellstyle.VerticalAlignment = vlignment;
            cellstyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            cellstyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            cellstyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            cellstyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            cellstyle.FillPattern = FillPattern.SolidForeground;
            cellstyle.FillForegroundColor = color;
            cellstyle.WrapText = true;
            cellstyle.Indention = 0;
            IFont font12 = hssfworkbook.CreateFont();
            font12.FontHeightInPoints = fontNo;
            font12.FontName = "微软雅黑";
            cellstyle.SetFont(font12);
            return cellstyle;
        }

    }
    /// <summary>
    /// 扩展方法
    /// </summary>
    public static class Extension
    {
        public static IList<T> ToList<T>(this DataTable dt)
        {
            var lst = new List<T>();
            var plist = new List<System.Reflection.PropertyInfo>(typeof(T).GetProperties());
            foreach (DataRow item in dt.Rows)
            {
                T t = System.Activator.CreateInstance<T>();
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    PropertyInfo info = plist.Find(p => p.Name == dt.Columns[i].ColumnName);
                    if (info != null)
                    {
                        if (!Convert.IsDBNull(item[i]))
                        {
                            info.SetValue(t, item[i], null);
                        }
                    }
                }
                lst.Add(t);
            }
            return lst;
        }



    }
}
